Amazon Athena Webアクセスログ、CloudFrontのログ、CloudTrailのログのパーティション設定を自動化する

Amazon Athena Webアクセスログ、CloudFrontのログ、CloudTrailのログのパーティション設定を自動化する

#AWS CloudTrail
#Amazon CloudFront
#Amazon Athena
#AWS
石川覚

石川覚

facebook logohatena logotwitter logo
Clock Icon2018.04.03

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

Webアクセスログ（ELB）、CloudFrontのログ、CloudTrailのログは、YYYY/MM/DD形式のフォルダの下に自動的に保存されます。Amazon Athenaは、S3上のログファイルに対してテーブル定義して、クエリを実行できます。しかし、大量のログファイルを高速かつコスト効率よくクエリするにはパーティションを設定する必要があります。今回はこの煩雑なパーティション設定を自動化する方法をご紹介します。

カラム名あり（Hive互換）とカラム名なしのパーティション

パーティションは、カラム名あり（Hive互換）パーティションとカラム名なしパーティションの２つに分類されます。

カラム名あり（Hive互換）パーティションは、フォルダ名がキーバリュー形式になっており、例えばyear=YYYY/month=MM/day=DD形式で保存されています。この形式はMSCK REPIRE TABLEを実行するとフォルダを再帰的にスキャンして、パーティションを自動設定できます。

一方、カラム名なしパーティションは、YYYY/MM/DD形式のフォルダの下に自動的に保存されます。

$ aws s3 ls s3://mybucket/devio2017-handson/07-elblogs/ --recursive
2017-08-21 12:42:42 11789573 devio2017-handson/07-elblogs/2015/01/01/part-r-00000-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt
2017-08-21 12:42:45 9012723 devio2017-handson/07-elblogs/2015/01/02/part-r-00006-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt
2017-08-21 12:42:48 11360522 devio2017-handson/07-elblogs/2015/01/03/part-r-00012-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt
2017-08-21 12:42:50 11899582 devio2017-handson/07-elblogs/2015/01/03/part-r-00017-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt
2017-08-21 12:42:53 8321364 devio2017-handson/07-elblogs/2015/01/04/part-r-00023-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt
2017-08-21 12:42:55 7864475 devio2017-handson/07-elblogs/2015/01/05/part-r-00029-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt
2017-08-21 12:42:56 9148117 devio2017-handson/07-elblogs/2015/01/06/part-r-00035-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt
2017-08-21 12:43:00 10019678 devio2017-handson/07-elblogs/2015/01/07/part-r-00041-ce65fca5-d6c6-40e6-b1f9-190cc4f93814.txt
：

Webアクセスログ（ELB）、CloudFrontのログ、CloudFrontのログ、Amazon Kinesis Data Firehoseなどはこの形式で保存されます。この形式はALTER TABLE ADD PARTITIONをパーティションごとに設定しなければなりません。この数が数百〜数万となると手作業では不可能です。以降では、ApacheのWebアクセスログを例に自動化の手順を解説します。なお、awscliとjqについては事前にインストールしてください。

例.ApacheのWebアクセスログのテーブルに対してパーティション設定する

テーブル定義は、以前紹介したAmazon Athena RegexSerDe を利用して CLB ログ / Apache Web のアクセスログを探索するのとおりです。年月日（year, month, day）にてパーティション設定しています。

CREATE EXTERNAL TABLE IF NOT EXISTS access_logs (
request_timestamp string,
elb_name string,
client_addrport string,
client_ip string,
client_port int,
backend_addrport string,
backend_ip string,
backend_port int,
request_processing_time decimal(8,6),
backend_processing_time decimal(8,6),
response_processing_time decimal(8,6),
elb_status_code string,
backend_status_code string,
received_bytes int,
sent_bytes int,
request string,
user_agent string,
ssl_cipher string,
ssl_protocol string
)
PARTITIONED BY (
year string,
month string,
day string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '1',
'input.regex' = '([^ ]*) ([^ ]*) (([0-9.]*):([0-9]*)|-) (([0-9.]*):([0-9]*)|-) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\\"[^\\"]*\\") (\\"[^\\"]*\\") ([^ ]*) ([^ ]*)[ ]*$'
)
LOCATION 's3://mybucket/devio2017-handson/07-clblogs'
TBLPROPERTIES ('has_encrypted_data'='false');

パーティション設定を自動生成する

では、どうやってパーティション設定を自動生成するかというと、MSCK REPIRE TABLEと同様にS3をスキャンして、ALTER TABLE ADD PARTITIONを自動生成します。自動生成した結果からピンポイントでパーティション設定したい場合などは、こちらのほうが使いやすいと思います。

  • dbname: dbnameを指定します
  • tablename: tablenameを指定します
  • s3bucket: データが保存されているバケットを指定します
  • s3prefix: フォルダのパスを指定します
  • profile: プロファイルを指定します（オプション）
#!/bin/sh

# Setting
dbname=default
tablename=access_logs
s3bucket=mybucket
s3prefix=devio2017-handson/07-elblogs
#profile="--profile=example_profile"

# get paths
paths=$(aws s3api list-objects --bucket ${s3bucket} --prefix ${s3prefix} ${profile} | grep -v '\$folder\$' | jq ".Contents[].Key" | awk -v FS='/' -v OFS='/' '{print $(NF-4), $(NF-3), $(NF-2), $(NF-1)}' | awk '!a[$0]++' | sort)

# generate ddl
for path in ${paths}
do
year=$(echo ${path} | awk -v FS='/' -v OFS='/' '{print $2}')
month=$(echo ${path} | awk -v FS='/' -v OFS='/' '{print $3}')
day=$(echo ${path} | awk -v FS='/' -v OFS='/' '{print $4}')
echo "ALTER TABLE ${dbname}.${tablename} ADD PARTITION (year='${year}',month='${month}',day='${day}') LOCATION 's3://${s3bucket}/${s3prefix}/${year}/${month}/${day}/';"
done

exit 0

上記のスクリプトを実行すると、以下のように自動生成されます。Athenaのコンソールは、複数のクエリをまとめて実行できないので、SQL WorkbenchなどのツールからSQL実行することになります。

$ ./accesslog_partitioner.sh
ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='01') LOCATION 's3://mybucket/07-elblogs/2015/01/01/';
ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='02') LOCATION 's3://mybucket/07-elblogs/2015/01/02/';
ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='03') LOCATION 's3://mybucket/07-elblogs/2015/01/03/';
ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='04') LOCATION 's3://mybucket/07-elblogs/2015/01/04/';
ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='05') LOCATION 's3://mybucket/07-elblogs/2015/01/05/';
ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='06') LOCATION 's3://mybucket/07-elblogs/2015/01/06/';
ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='07') LOCATION 's3://mybucket/07-elblogs/2015/01/07/';
：

パーティション設定を自動設定する

完全に自動実行したい場合は、以下のようにawscliでDDLを同期的に実行します。(同期的に実行する方法はAWS CLI で Athena のクエリ実行を同期的に行うを参考にさせていただきました)

  • dbname: dbnameを指定します
  • tablename: tablenameを指定します
  • s3bucket: データが保存されているバケットを指定します
  • s3prefix: フォルダのパスを指定します
  • outputlocation: 実行結果を保存するディレクトリを指定します
  • profile: プロファイルを指定します（オプション）
#!/bin/sh

# Setting
dbname=default
tablename=access_logs
s3bucket=mybucket
s3prefix=devio2017-handson/07-elblogs
outputlocation=s3://s3-staging-dir/tmp/
#profile="--profile=example_profile"

MAX_RETRY=30
FETCH_INTERVAL_SECONDS=1

# get paths
paths=$(aws s3api list-objects --bucket ${s3bucket} --prefix ${s3prefix} ${profile} | grep -v '\$folder\$' | jq ".Contents[].Key" | awk -v FS='/' -v OFS='/' '{print $(NF-4), $(NF-3), $(NF-2), $(NF-1)}' | awk '!a[$0]++' | sort)

# generate ddl
for path in ${paths}
do
year=$(echo ${path} | awk -v FS='/' -v OFS='/' '{print $2}')
month=$(echo ${path} | awk -v FS='/' -v OFS='/' '{print $3}')
day=$(echo ${path} | awk -v FS='/' -v OFS='/' '{print $4}')
echo "ALTER TABLE ${dbname}.${tablename} ADD PARTITION (year='${year}',month='${month}',day='${day}') LOCATION 's3://${s3bucket}/${s3prefix}/${year}/${month}/${day}/';"
query_execution_id=$(aws athena start-query-execution --query-string "ALTER TABLE ${dbname}.${tablename} ADD PARTITION (year='${year}',month='${month}',day='${day}') LOCATION 's3://${s3bucket}/${s3prefix}/${year}/${month}/${day}/';" --result-configuration OutputLocation="${outputlocation}" ${profile} | jq -r '.QueryExecutionId')

try_cnt=1
while true
do
if [ ${try_cnt} -ge ${MAX_RETRY} ] ; then
echo "Error: timeout" >&2
break
else
sleep ${FETCH_INTERVAL_SECONDS}
fi

query_execution_result=$(aws athena get-query-execution --query-execution-id ${query_execution_id} ${profile})
query_state=$(echo ${query_execution_result} | jq -r '.QueryExecution.Status.State')

if [ ${query_state} = 'SUCCEEDED' ] ; then
echo "${query_state}"
break
elif [ ${query_state} = 'FAILED' ] ; then
echo "${query_state}" >&2
break
fi

try_cnt=$(expr ${try_cnt} + 1)
done
done

exit 0

実行すると、DDLと実行結果（SUCCEEDED、FAILED, Error: timeout）を表示します。すでにパーティションが登録済みの場合は、FAILEDになります。

$ ./accesslog_partitioner.sh
ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='01') LOCATION 's3://mybucket/devio2017-handson/07-elblogs/2015/01/01/';
SUCCEEDED
ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='02') LOCATION 's3://mybucket/devio2017-handson/07-elblogs/2015/01/02/';
SUCCEEDED
ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='03') LOCATION 's3://mybucket/devio2017-handson/07-elblogs/2015/01/03/';
SUCCEEDED
ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='04') LOCATION 's3://mybucket/devio2017-handson/07-elblogs/2015/01/04/';
SUCCEEDED
ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='05') LOCATION 's3://mybucket/devio2017-handson/07-elblogs/2015/01/05/';
SUCCEEDED
ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='06') LOCATION 's3://mybucket/devio2017-handson/07-elblogs/2015/01/06/';
SUCCEEDED
ALTER TABLE default.access_logs ADD PARTITION (year='2015',month='01',day='07') LOCATION 's3://mybucket/devio2017-handson/07-elblogs/2015/01/07/';
SUCCEEDED
：

最後に

Amazon Athena とブログタイトルに書きましたが、テーブル定義（メタデータ）の登録先は Glue Data Catalog ですので、Amazon Redshift Spectrum、Amazon EMR、AWS Glueにおいても同様に利用可能です。

参考

Webアクセスログ、CloudFrontのログ、CloudFrontのログに関するテーブル定義につきましては、以下のブログを御覧ください。

Amazon Athena RegexSerDe を利用して CLB ログ / Apache Web のアクセスログを探索する

Amazon Athena RegexSerDe を利用して ALB ログを探索する

Amazon Athena CloudTrailSerdeを利用してAWSのアクティビティを探索する

新機能 AWSCLIから Amazon Athena のクエリを実行する

Share this article

facebook logohatena logotwitter logo

関連記事

CloudTrailイベントのレコードについてサービス名称が分からない際の確認方法について

CloudTrailイベントのレコードについてサービス名称が分からない際の確認方法について

User avatar
Lei
2024.12.10
CloudWatchでセキュリティ可観測性を向上させるワークショップに参加。可視化から対応までの一連の流れをコスパ良く体験できました #AWSreInvent #COP340

CloudWatchでセキュリティ可観測性を向上させるワークショップに参加。可視化から対応までの一連の流れをコスパ良く体験できました #AWSreInvent #COP340

User avatar
川原征大
2024.12.05
[アップデート]CloudTrail Lake で高度なイベントセレクターがサポートされました

[アップデート]CloudTrail Lake で高度なイベントセレクターがサポートされました

User avatar
あしざわ
2024.11.17
EventBridge を使って特定の IP 以外からのアクセスキー利用をメール通知してみる

EventBridge を使って特定の IP 以外からのアクセスキー利用をメール通知してみる

User avatar
ヒラネ
2024.11.16
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.